﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using BC;
using BE;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using COMExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Threading;
namespace ModuleDuoc.Report
{
    public partial class frmTK_TonKho : ModuleDuoc.Base.frmTK_Base
    {
        public frmTK_TonKho()
        {
            InitializeComponent();
        }
        public override void btnView_Click(object sender, EventArgs e)
        {
            base.btnView_Click(sender, e);
            string sql = "zD_BC_TONKHO";
            System.Data.SqlClient.SqlParameter tungay = new System.Data.SqlClient.SqlParameter("@tungay", this.dtpFrom.Value);
            System.Data.SqlClient.SqlParameter denngay = new System.Data.SqlClient.SqlParameter("@denngay", this.dtpFrom.Value);
            System.Data.SqlClient.SqlParameter mavt = new System.Data.SqlClient.SqlParameter("@mavt", "");
            System.Data.SqlClient.SqlParameter manhom = new System.Data.SqlClient.SqlParameter("@manhom", "");
            System.Data.SqlClient.SqlParameter nhacc = new System.Data.SqlClient.SqlParameter("@nhacc", "");
            System.Data.SqlClient.SqlParameter wherein = new System.Data.SqlClient.SqlParameter("@wherein", "");

            SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.StoredProcedure, sql,
                tungay,
                denngay,
                mavt,
                manhom,
                nhacc,
                wherein);
            string sql_view = "SELECT mavt,tenvt,dvt,manhom,tennhom,sum(tondauky) as tondauky, SUM(tiendauky) as tiendauky,";
            sql_view += "sum(slnhap) as slnhap,avg(dongianhap) as dgnhap,sum(tiennhap) as tiennhap,sum(slxuat) as slxuat,";
            sql_view += "AVG(dongiaxuat) as dongiaxuat,";
            sql_view += "sum(tienxuat) as tienxuat,sum(toncuoi)as toncuoi,SUM(tiencuoi) as tiencuoi";
            sql_view += "from zD_bcTonkhoth WHERE mavt >0";
            sql_view += "GROUP BY mavt,tenvt,dvt,manhom,tennhom";
            sql_view += "ORDER BY tenvt,mavt";

            this.dgvData.DataSource = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql_view).Tables[0];

        }

        private void frmTK_TonKho_Load(object sender, EventArgs e)
        {
            this.dgvData.AutoGenerateColumns = false;
            this.prgXuatExcel.Visible = false;
        }
        private float getDonGiaNhap(string mavach)
        {
            string sql = "select dongianhap,vat from zD_PHIEUNHAPKHOCT where mavach=" + mavach;
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            float dongianhap = 0;
            if (dt.Rows.Count > 0)
            {
                dongianhap = float.Parse(dt.Rows[0]["dongianhap"].ToString()) - (float.Parse(dt.Rows[0]["dongianhap"].ToString()) * float.Parse(dt.Rows[0]["vat"].ToString()) / 100);
                
            }
            return dongianhap;
        }
        public override void btnExcel_Click(object sender, EventArgs e)
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            base.btnExcel_Click(sender, e);
            double progress = 0;
            this.prgXuatExcel.Maximum = dgvData.Rows.Count;

            COMExcel.Application exApp = new COMExcel.Application();
            // Thêm file temp xls
            COMExcel.Workbook exBook = exApp.Workbooks.Add(
                      COMExcel.XlWBATemplate.xlWBATWorksheet);
            // Lấy sheet 1.
            COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
            exSheet.Name = "KIEM KE";

            COMExcel.Range RE_TIEUDE = (COMExcel.Range)exSheet.get_Range("A1", "A1");
            RE_TIEUDE.Value2 = "Bệnh viện MEDLATEC";
            RE_TIEUDE.EntireRow.Font.Name = "Arial";
            RE_TIEUDE.EntireRow.Font.Size = 14;
            RE_TIEUDE.EntireRow.Font.Bold = true;
            exSheet.get_Range("A1", "J1").Merge(false);

            COMExcel.Range RE_TIEUDE2 = (COMExcel.Range)exSheet.get_Range("A2", "A2");
            RE_TIEUDE2.Value2 = "Bảng tổng hợp tồn kho thuốc tháng 11/2012";
            RE_TIEUDE2.EntireRow.Font.Name = "Arial";
            RE_TIEUDE2.EntireRow.Font.Size = 12;
            RE_TIEUDE2.EntireRow.Font.Bold = true;
            exSheet.get_Range("A2", "J2").Merge(false);

            COMExcel.Range TIEUDE_SOMAHANGTONKHO = (COMExcel.Range)exSheet.get_Range("D3", "D3");
            TIEUDE_SOMAHANGTONKHO.Value2 = "Số mã hàng tồn kho : ";
            TIEUDE_SOMAHANGTONKHO.EntireRow.Font.Name = "Arial";
            TIEUDE_SOMAHANGTONKHO.EntireRow.Font.Size = 10;
            TIEUDE_SOMAHANGTONKHO.EntireRow.Font.Bold = true;

            COMExcel.Range V_SOMAHANGTONKHO = (COMExcel.Range)exSheet.get_Range("E3", "E3");
            V_SOMAHANGTONKHO.Value2 = dgvData.Rows.Count;
            V_SOMAHANGTONKHO.EntireRow.Font.Name = "Arial";
            V_SOMAHANGTONKHO.EntireRow.Font.Size = 10;
            V_SOMAHANGTONKHO.EntireRow.Font.Bold = true;


            COMExcel.Range TIEUDE_TIENTHIEUTHUA = (COMExcel.Range)exSheet.get_Range("G3", "G3");
            TIEUDE_TIENTHIEUTHUA.Value2 = "Tiền thiếu thừa : ";
            TIEUDE_TIENTHIEUTHUA.EntireRow.Font.Name = "Arial";
            TIEUDE_TIENTHIEUTHUA.EntireRow.Font.Size = 10;
            TIEUDE_TIENTHIEUTHUA.EntireRow.Font.Bold = true;


            COMExcel.Range TIEUDE_STT = (COMExcel.Range)exSheet.get_Range("A4", "A4");
            TIEUDE_STT.Value2 = "STT";
            TIEUDE_STT.EntireRow.Font.Name = "Arial";
            TIEUDE_STT.EntireRow.Font.Size = 10;
            TIEUDE_STT.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_NGAN = (COMExcel.Range)exSheet.get_Range("B4", "B4");
            TIEUDE_NGAN.Value2 = "Ngăn";
            TIEUDE_NGAN.EntireRow.Font.Name = "Arial";
            TIEUDE_NGAN.EntireRow.Font.Size = 10;
            TIEUDE_NGAN.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_Ma = (COMExcel.Range)exSheet.get_Range("C4", "C4");
            TIEUDE_Ma.Value2 = "Mã";
            TIEUDE_Ma.EntireRow.Font.Name = "Arial";
            TIEUDE_Ma.EntireRow.Font.Size = 10;
            TIEUDE_Ma.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_TENTHUOC = (COMExcel.Range)exSheet.get_Range("D4", "D4");
            TIEUDE_TENTHUOC.Value2 = "Tên thuốc";
            TIEUDE_TENTHUOC.EntireRow.Font.Name = "Arial";
            TIEUDE_TENTHUOC.EntireRow.Font.Size = 10;
            TIEUDE_TENTHUOC.EntireRow.Font.Bold = true;
            

            COMExcel.Range TIEUDE_DVT = (COMExcel.Range)exSheet.get_Range("E4", "E4");
            TIEUDE_DVT.Value2 = "ĐVT";
            TIEUDE_DVT.EntireRow.Font.Name = "Arial";
            TIEUDE_DVT.EntireRow.Font.Size = 10;
            TIEUDE_DVT.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_TON = (COMExcel.Range)exSheet.get_Range("F4", "F4");
            TIEUDE_TON.Value2 = "Tồn";
            TIEUDE_TON.EntireRow.Font.Name = "Arial";
            TIEUDE_TON.EntireRow.Font.Size = 10;
            TIEUDE_TON.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_TONTHUCTE = (COMExcel.Range)exSheet.get_Range("G4", "G4");
            TIEUDE_TONTHUCTE.Value2 = "Tồn thực tế";
            TIEUDE_TONTHUCTE.EntireRow.Font.Name = "Arial";
            TIEUDE_TONTHUCTE.EntireRow.Font.Size = 10;
            TIEUDE_TONTHUCTE.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_CHENHLECH = (COMExcel.Range)exSheet.get_Range("H4", "H4");
            TIEUDE_CHENHLECH.Value2 = "Chênh lệch";
            TIEUDE_CHENHLECH.EntireRow.Font.Name = "Arial";
            TIEUDE_CHENHLECH.EntireRow.Font.Size = 10;
            TIEUDE_CHENHLECH.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_DONGIA = (COMExcel.Range)exSheet.get_Range("I4", "I4");
            TIEUDE_DONGIA.Value2 = "Đơn giá";
            TIEUDE_DONGIA.EntireRow.Font.Name = "Arial";
            TIEUDE_DONGIA.EntireRow.Font.Size = 10;
            TIEUDE_DONGIA.EntireRow.Font.Bold = true;

            COMExcel.Range TIEUDE_THANHTIEN = (COMExcel.Range)exSheet.get_Range("J4", "J4");
            TIEUDE_THANHTIEN.Value2 = "Thành tiền";
            TIEUDE_THANHTIEN.EntireRow.Font.Name = "Arial";
            TIEUDE_THANHTIEN.EntireRow.Font.Size = 10;
            TIEUDE_THANHTIEN.EntireRow.Font.Bold = true;

            int start = 5;
            if (dgvData.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    progress = i + 1;
                    // hien thi chay % tren thanh process, lam tron 2 ki tu
                    lblText.Text = Math.Round(((progress / dgvData.Rows.Count) * 100), 2).ToString() + "%";
                    prgXuatExcel.Value = i + 1;
                    Thread.Sleep(2);
                    Application.DoEvents();
                    //STT
                    COMExcel.Range V_STT = (COMExcel.Range)exSheet.get_Range("A" + (i + start), "A" + (i + start));
                    V_STT.Value2 = i.ToString();
                    V_STT.EntireColumn.AutoFit();

                    //MAVVACH
                    COMExcel.Range V_MAVACH = (COMExcel.Range)exSheet.get_Range("C" + (i + start), "C" + (i + start));
                    V_MAVACH.Value2 = dgvData.Rows[i].Cells["mavach"].Value.ToString();
                    V_MAVACH.EntireColumn.AutoFit();

                    //TEVT
                    COMExcel.Range V_TENVT = (COMExcel.Range)exSheet.get_Range("D" + (i + start), "D" + (i + start));
                    V_TENVT.Value2 = dgvData.Rows[i].Cells["tenvt"].Value.ToString();
                    V_TENVT.EntireColumn.AutoFit();

                    //DVT
                    COMExcel.Range V_DVT = (COMExcel.Range)exSheet.get_Range("E" + (i + start), "E" + (i + start));
                    V_DVT.Value2 = dgvData.Rows[i].Cells["dvt"].Value.ToString();
                    V_DVT.EntireColumn.AutoFit();

                    //TON_CUOI
                    COMExcel.Range V_TONCUOI = (COMExcel.Range)exSheet.get_Range("F" + (i + start), "F" + (i + start));
                    V_TONCUOI.Value2 = dgvData.Rows[i].Cells["toncuoi"].Value.ToString();
                    V_TONCUOI.EntireColumn.AutoFit();

                    //THUC_TE
                    COMExcel.Range V_THUCTE = (COMExcel.Range)exSheet.get_Range("G" + (i + start), "G" + (i + start));
                    V_THUCTE.Value2 = dgvData.Rows[i].Cells["toncuoi"].Value.ToString();
                    V_THUCTE.EntireColumn.AutoFit();

                    //CHENH LECH
                    COMExcel.Range V_CHENHLECH = (COMExcel.Range)exSheet.get_Range("H" + (i + start), "H" + (i + start));
                    V_CHENHLECH.Value2 = "=F" + (i + start) + "-" + "G" + (i + start);
                    V_CHENHLECH.EntireColumn.AutoFit();

                    //DONGIA
                    COMExcel.Range V_DONGIA = (COMExcel.Range)exSheet.get_Range("I" + (i + start), "I" + (i + start));
                    V_DONGIA.Value2 = getDonGiaNhap(dgvData.Rows[i].Cells["mavach"].Value.ToString());
                    V_DONGIA.EntireColumn.AutoFit();

                    //THANHTIEN
                    COMExcel.Range V_THANHTIEN = (COMExcel.Range)exSheet.get_Range("J" + (i + start), "J" + (i + start));
                    V_THANHTIEN.Value2 = "=I" + (i + start) + "*" + "G" + (i + start);
                    V_THANHTIEN.EntireColumn.AutoFit();
                }
            }

            exSheet.get_Range("A4", "J4").Interior.ColorIndex = 6;
            exSheet.get_Range("A4", "J4").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;
            exSheet.get_Range("A4", "J4").Application.ActiveWindow.FreezePanes = true;
            //exSheet.get_Range("A1", "A2").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;
            exSheet.get_Range("A1", "J" + dgvData.Rows.Count).Borders.ColorIndex = 1;

            #region SAVE FILE
            // Ẩn chương trình
            exApp.Visible = false;

            // Save file
            exBook.SaveAs("D:\\KIEMKE_DUOC.xls", COMExcel.XlFileFormat.xlWorkbookNormal,
                            null, null, false, false,
                            COMExcel.XlSaveAsAccessMode.xlExclusive,
                            false, false, false, false, false);
            exApp.Quit();
            MessageBox.Show("Lưu thành công tại : D:\\KIEMKE_DUOC.xls", "Thông báo");

            FileInfo fi = new FileInfo("D:\\KIEMKE_DUOC.xls");
            if (fi.Exists)
            {
                System.Diagnostics.Process.Start(@"D:\\KIEMKE_DUOC.xls");
            }
            else
            {
                //
            }
            #endregion

        }
    }
}
